package ru.ssau.customerManagement.model;

import com.google.common.base.Preconditions;
import ru.ssau.customerManagement.model.db.DatabaseHelper;
import ru.ssau.customerManagement.model.db.ResultSetParser;
import ru.ssau.customerManagement.model.entities.Customer;
import ru.ssau.customerManagement.model.entities.CustomerLocation;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by illabefat on 20.12.14.
 */
public class DBModel implements Model {
    private final DatabaseHelper databaseHelper;

    public DBModel(DatabaseHelper databaseHelper) {
        Preconditions.checkNotNull(databaseHelper);
        this.databaseHelper = databaseHelper;
    }

    @Override
    public Customer getCustomer(Long id) {
        String query = String.format("select * from customers where id = %d", id);
        Customer res = databaseHelper.executeSelect(query, new ResultSetParser<Customer>() {
            @Override
            public Customer parse(ResultSet resultSet) throws SQLException {
                if (resultSet.next())
                {
                    long id  = resultSet.getLong("id");
                    String name = resultSet.getString("name");
                    String description = resultSet.getString("description");

                    return new Customer(id,name, description );
                }
                else
                    return null;
            }
        });
        return res;
    }

    @Override
    public CustomerLocation getCustomerLocation(Long id) {
        String query = String.format("select * from customer_locations where id = %d", id);
        CustomerLocation res = databaseHelper.executeSelect(query, new ResultSetParser<CustomerLocation>() {
            @Override
            public CustomerLocation parse(ResultSet resultSet) throws SQLException {
                if (resultSet.next())
                {
                    long id  = resultSet.getLong("id");
                    long customerId = resultSet.getLong("customer_id");
                    String address = resultSet.getString("address");

                    return new CustomerLocation(id,customerId,address);
                }
                else
                    return null;
            }
        });
        return res;
    }

    @Override
    public List<CustomerLocation> getCustomerLocationsByCustomer(Long customerId) {
        String query = String.format("select * from customer_locations where customer_id = %d", customerId);
        List<CustomerLocation> res = databaseHelper.executeSelect(query, new ResultSetParser<List<CustomerLocation>>() {
            @Override
            public List<CustomerLocation> parse(ResultSet resultSet) throws SQLException {
                List<CustomerLocation> res = new ArrayList<CustomerLocation>();
                while (resultSet.next())
                {
                    long id  = resultSet.getLong("id");
                    long customerId = resultSet.getLong("customer_id");
                    String address = resultSet.getString("address");

                    res.add(new CustomerLocation(id,customerId,address));
                }
                return res;
            }
        });
        return res;
    }

    @Override
    public List<Customer> getCustomers() {
        String query = String.format("select * from customers");
        List<Customer> res = databaseHelper.executeSelect(query, new ResultSetParser<List<Customer>>() {
            @Override
            public List<Customer> parse(ResultSet resultSet) throws SQLException {
                List<Customer> res = new ArrayList<Customer>();
                while (resultSet.next())
                {
                    long id  = resultSet.getLong("id");
                    String name = resultSet.getString("name");
                    String description = resultSet.getString("description");

                    res.add(new Customer(id,name, description ));
                }
                return res;
            }
        });
        return res;
    }

    @Override
    public void addCustomer(Customer customer) {
        String query = String.format("insert into customers(id, name, description) values (%d, '%s', '%s')",
                customer.getId(), customer.getName(), customer.getDescription());
        databaseHelper.executeStatement(query);
    }

    @Override
    public void addCustomerLocation(CustomerLocation location) {
        String query = String.format("insert into customer_locations(id, customer_id, address) values (%d, %d, '%s')",
                location.getId(), location.getCustomerId(), location.getAddress());
        databaseHelper.executeStatement(query);
    }

    @Override
    public void changeCustomer(Customer newCustomer) {
        String query = String.format("update customers set name = '%s', description = '%s' where id = %d",
                newCustomer.getName(), newCustomer.getDescription(), newCustomer.getId());
        databaseHelper.executeStatement(query);

    }

    @Override
    public void changeCustomerLocation(CustomerLocation location) {
        String query = String.format("update customer_locations set address = '%s' where id = %d",
                location.getAddress(), location.getId());
        databaseHelper.executeStatement(query);
    }

    @Override
    public void removeCustomer(Long id) {
        String query = String.format("delete from customers where id = %d", id);
        databaseHelper.executeStatement(query);
    }

    @Override
    public void removeCustomerLocation(Long id) {
        String query = String.format("delete from customer_locations where id = %d", id);
        databaseHelper.executeStatement(query);
    }

    @Override
    public boolean isCustomerLocked(Long id) {
        String query = String.format("select * from locked_customers where customer_id = %d", id);
        Boolean res = databaseHelper.executeSelect(query, new ResultSetParser<Boolean>() {
            @Override
            public Boolean parse(ResultSet resultSet) throws SQLException {
                if (resultSet.next())
                {
                    return true;
                }
                else
                    return false;
            }
        });
        return res;
    }

    @Override
    public synchronized boolean lockCustomer(Long id) {
        if (isCustomerLocked(id))
            return false;

        String query = String.format("insert into locked_customers (customer_id) values (%d)", id);
        databaseHelper.executeStatement(query);

        return true;
    }

    @Override
    public void unlockCustomer(Long id) {
        String query = String.format("delete from locked_customers where customer_id = %d", id);
        databaseHelper.executeStatement(query);
    }

    @Override
    public boolean isCustomerLocationLocked(Long id) {
        String query = String.format("select * from locked_customer_locations where customer_id = %d", id);
        Boolean res = databaseHelper.executeSelect(query, new ResultSetParser<Boolean>() {
            @Override
            public Boolean parse(ResultSet resultSet) throws SQLException {
                if (resultSet.next())
                {
                    return true;
                }
                else
                    return false;
            }
        });
        return res;
    }

    @Override
    public synchronized boolean lockCustomerLocation(Long id) {
        if (isCustomerLocationLocked(id))
            return false;

        String query = String.format("insert into locked_customer_locations (customer_location_id) values (%d)", id);
        databaseHelper.executeStatement(query);

        return true;
    }

    @Override
    public void unlockCustomerLocation(Long id) {
        String query = String.format("delete from locked_customer_locations where customer_location_id = %d", id);
        databaseHelper.executeStatement(query);
    }

    @Override
    public List<Customer> findCustomersByName(String name) {
        String query = "select * from customers where name like '%" + name + "%'";
        List<Customer> res = databaseHelper.executeSelect(query, new ResultSetParser<List<Customer>>() {
            @Override
            public List<Customer> parse(ResultSet resultSet) throws SQLException {
                List<Customer> res = new ArrayList<Customer>();
                while (resultSet.next())
                {
                    long id  = resultSet.getLong("id");
                    String name = resultSet.getString("name");
                    String description = resultSet.getString("description");

                    res.add(new Customer(id,name, description ));
                }
                return res;
            }
        });
        return res;
    }

    @Override
    public List<CustomerLocation> findCustomerLocationsByAddress(String address) {
        String query = "select * from customer_locations where address like '%" + address + "%'";
        List<CustomerLocation> res = databaseHelper.executeSelect(query, new ResultSetParser<List<CustomerLocation>>() {
            @Override
            public List<CustomerLocation> parse(ResultSet resultSet) throws SQLException {
                List<CustomerLocation> res = new ArrayList<CustomerLocation>();
                while (resultSet.next())
                {
                    long id  = resultSet.getLong("id");
                    long customerId = resultSet.getLong("customer_id");
                    String address = resultSet.getString("address");

                    res.add(new CustomerLocation(id,customerId,address));
                }
                return res;
            }
        });
        return res;
    }

    @Override
    public List<CustomerLocation> getAllLocations() {
        String query = String.format("select * from customer_locations");
        List<CustomerLocation> res = databaseHelper.executeSelect(query, new ResultSetParser<List<CustomerLocation>>() {
            @Override
            public List<CustomerLocation> parse(ResultSet resultSet) throws SQLException {
                List<CustomerLocation> res = new ArrayList<CustomerLocation>();
                while (resultSet.next())
                {
                    long id  = resultSet.getLong("id");
                    long customerId = resultSet.getLong("customer_id");
                    String address = resultSet.getString("address");

                    res.add(new CustomerLocation(id,customerId,address));
                }
                return res;
            }
        });
        return res;
    }
}
